package com.vimi8.ebb.service;

import com.vimi8.ebb.dto.LogisticsBusinessDTO1;
import com.vimi8.ebb.model.Logistics;
import com.vimi8.ebb.model.LogisticsBusiness;
import com.vimi8.ebb.sqlprovider.LogisticsSqlProvider;
import org.apache.ibatis.annotations.*;
import org.springframework.security.access.method.P;

import java.util.HashMap;
import java.util.List;

/**
 * 物流信息service
 * Created by xiehuang on 2017/1/19.
 */
@Mapper
public interface LogisticsService {
    /**
     * 添加物流信息
     *
     * @param logistics 物流信息对象
     * @return 影响的行数
     */
    @Insert("INSERT INTO logistics(id, branch_name, branch_address, branch_address_code, affiliation_id, principal, contact, " +
            "staffs, vehicles, shipping_order_id, shipping_address, shipping_address_code, delivery_area, delivery_area_code, " +
            "create_time, update_time) " +
            "VALUES(#{id}, #{branchName}, #{branchAddress}, #{branchAddressCode}, #{affiliationId}, #{principal}, #{contact}, " +
            "#{staffs}, #{vehicles}, #{shippingOrderId}, #{shippingAddress}, #{shippingAddressCode}, #{deliveryArea}, " +
            "#{deliveryAreaCode}, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())")
    public int insertLogistics(Logistics logistics);

    /**
     * 添加物流业务量
     *
     * @param logisticsBusiness 物流业务量
     * @return 影响的行数
     */
    @Insert("INSERT INTO logistics_business(id, logistics_id, month_out_amount, month_in_amount, statistics_time) " +
            "VALUES(#{id}, #{logisticsId}, #{monthOutAmount}, #{monthInAmount}, #{statisticsTime})")
    public int insertLogisticsBusiness(LogisticsBusiness logisticsBusiness);

    @InsertProvider(type = LogisticsSqlProvider.class, method = "getInsertLogisticSql")
    public int batchLogistics(@Param("logisticsList") List<Logistics> logisticsList);

    @InsertProvider(type = LogisticsSqlProvider.class, method = "getInsertLogisticBusinessSql")
    public int batchLogisticsBusiness(@Param("logisticsList") List<Logistics> logisticsList);

    /**
     * 修改物流信息
     *
     * @param logistics 物流信息对象
     * @return 影响的行数
     */
    @Update("UPDATE logistics SET branch_name=#{branchName}, branch_address=#{branchAddress}, branch_address_code=#{branchAddressCode}, " +
            "affiliation_id=#{affiliationId}, principal=#{principal}, contact=#{contact}, staffs=#{staffs}, vehicles=#{vehicles}, " +
            "shipping_order_id=#{shippingOrderId}, shipping_address=#{shippingAddress}, shipping_address_code=#{shippingAddressCode}, " +
            "delivery_area=#{deliveryArea}, delivery_area_code=#{deliveryAreaCode}, update_time=CURRENT_TIMESTAMP() " +
            "WHERE id = #{id}")
    public int updateLogistics(Logistics logistics);

    /**
     * 删除物流信息
     *
     * @param id
     * @return 影响的行数
     */
    @Update("DELETE FROM logistics where id=#{id}")
    public int deleteLogistics(String id);

    /**
     * 删除物流业务量信息
     *
     * @param id
     * @return 影响的行数
     */
    @Delete("DELETE FROM logistics_business where id=#{id}")
    public int deleteLogisticsBusiness(String id);

    /**
     * 根据物流id批量删除物流信息
     * @param ids
     * @return 影响的行数
     */
    @Delete("DELETE l,b FROM logistics AS l LEFT JOIN logistics_business AS b ON " +
            "l.id=b.logistics_id WHERE l.id IN (${ids})")
    public Integer deleteLogisticsByIds(@Param("ids") String ids);

    /**
     * 查询物流信息的条数
     *
     * @param addressCode 区域
     * @param branchName  网点名称
     * @return 物流信息列表
     */
    @Select("<script>" +
            "SELECT count(*) FROM logistics WHERE 1=1 " +
            "<if test=\"addressCode!=null and addressCode!='' \">AND branch_address_code LIKE CONCAT('%',#{addressCode},'%') </if> " +
            "<if test=\"branchName!=null and branchName!='' \">AND branch_name LIKE CONCAT('%',#{branchName},'%') </if> " +
            "</script>")
    public int getLogisticsCount(
            @Param(value = "addressCode") String addressCode,
            @Param(value = "branchName") String branchName);

    /**
     * 物流分析
     *
     * @param addressCode 区域编码
     * @param next        当前区域的下一个
     * @return 物流分析结果
     */
    @Select("<script>" +
            "SELECT IFNULL(SUM(lb.monthOutAmount),0) AS monthOutAmount, IFNULL(SUM(lb.monthInAmount),0) AS monthInAmount " +
            "FROM logistics l LEFT JOIN " +
            "(SELECT a.id, a.logistics_id, b.monthInAmount, b.monthOutAmount, a.statistics_time " +
            "FROM logistics_business AS a, " +
            "(SELECT logistics_id AS logisticsId, SUM(month_in_amount) AS monthInAmount, SUM(month_out_amount) AS monthOutAmount, statistics_time AS statisticsTime " +
            "FROM logistics_business " +
            "WHERE DATE_FORMAT(statistics_time,'%Y-%m')=#{currentTime} " +
            "GROUP BY logistics_id) AS b " +
            "WHERE a.logistics_id=b.logisticsId AND a.statistics_time=b.statisticsTime " +
            ") AS lb " +
            "ON l.id=lb.logistics_id " +
            "WHERE l.branch_address_code BETWEEN #{addressCode} AND #{next} AND l.branch_address_code!=#{next} " +
            "</script>")
    public HashMap<String, Object> getLogisticsAnalysis(
            @Param(value = "addressCode") String addressCode,
            @Param(value = "next") long next,
            @Param(value = "currentTime") String currentTime);

    @Select("<script>" +
            "SELECT id, branch_name AS branchName, branch_address AS branchAddress, branch_address_code AS branchAddressCode, " +
            "affiliation_id AS affiliationId, principal, contact, staffs, vehicles, trade_statistics_time AS tradeStatisticsTime, " +
            "month_out_amount AS monthOutAmount, month_in_amount AS monthInAmount, shipping_order_id AS shippingOrderId, " +
            "shipping_address AS shippingAddress, shipping_address_code AS shippingAddressCode, delivery_area AS deliveryArea, " +
            "delivery_area_code AS deliveryAreaCode, create_time AS createTime, update_time AS updateTime " +
            "FROM logistics WHERE 1=1 " +
            "<if test=\"addressCode!=null and addressCode!='' \">AND branch_address_code LIKE CONCAT('%',#{addressCode},'%') </if> " +
            "<if test=\"branchName!=null and branchName!='' \">AND branch_name LIKE CONCAT('%',#{branchName},'%') </if> " +
            "<if test=\"staffsOrder!=null and staffsOrder!='' and (statisticsTimeOrder==null or statisticsTimeOrder=='')\"> ORDER BY staffs ${staffsOrder} </if> " +
            "<if test=\"(staffsOrder==null or staffsOrder=='') and statisticsTimeOrder!=null and statisticsTimeOrder!='' \"> ORDER BY trade_statistics_time ${statisticsTimeOrder} </if> " +
            "<if test=\"staffsOrder!=null and staffsOrder!='' and statisticsTimeOrder!=null and statisticsTimeOrder!='' \"> ORDER BY staffs ${staffsOrder}, trade_statistics_time ${statisticsTimeOrder} </if> " +
            "LIMIT #{offset}, #{size}" +
            "</script>")
    public List<Logistics> getLogistics(
            @Param(value = "offset") int offset,
            @Param(value = "size") int size,
            @Param(value = "addressCode") String addressCode,
            @Param(value = "branchName") String branchName,
            @Param(value = "staffsOrder") String staffsOrder,
            @Param(value = "statisticsTimeOrder") String statisticsTimeOrder);

    /**
     * 查询最新物流信息的条数
     *
     * @param addressCode    区域
     * @param branchName     网点名称
     * @param statisticsTime 统计时间
     * @return 物流信息列表
     */
    @Select("<script>" +
            "SELECT COUNT(1) " +
            "FROM logistics as l left join " +
            "(SELECT a.id, a.logistics_id, a.month_in_amount, a.month_out_amount, a.statistics_time " +
            "FROM logistics_business AS a, (SELECT logistics_id AS logisticsId, MAX(statistics_time) AS statisticsTime FROM logistics_business GROUP BY logistics_id) AS b " +
            "WHERE a.logistics_id=b.logisticsId AND a.statistics_time=b.statisticsTime " +
            ") AS lb on l.id=lb.logistics_id where 1=1 " +
            "<if test=\"addressCode!=null and addressCode!='' \">AND l.branch_address_code LIKE CONCAT('%',#{addressCode},'%') </if> " +
            "<if test=\"branchName!=null and branchName!='' \">AND l.branch_name LIKE CONCAT('%',#{branchName},'%') </if> " +
            "<if test=\"statisticsTime!=null and statisticsTime!='' \">" +
            " and lb.statistics_time &gt;= #{statisticsTime}  </if> " +
            "<if test=\"endTime!=null and endTime!='' \">" +
            " and lb.statistics_time &lt; #{endTime}  </if> " +
            "</script>")
    public int getLogisticsLatestCount(
            @Param(value = "addressCode") String addressCode,
            @Param(value = "branchName") String branchName,
            @Param(value = "statisticsTime") String statisticsTime,
            @Param(value = "endTime") String endTime
    );

    @Select("<script>" +
            "SELECT l.id AS logisticsId, l.branch_name AS branchName, l.branch_address AS branchAddress, l.branch_address_code AS branchAddressCode, " +
            "l.affiliation_id AS affiliationId, l.principal, l.contact, l.staffs, l.vehicles, l.delivery_area AS deliveryArea, l.delivery_area_code AS deliveryAreaCode, " +
            "lb.id AS logisticsBusinessId, lb.month_out_amount AS monthOutAmount, lb.month_in_amount AS monthInAmount, lb.statistics_time AS statisticsTime " +
            "FROM logistics as l left join " +
            "(SELECT a.id, a.logistics_id, a.month_in_amount, a.month_out_amount, a.statistics_time " +
            "FROM logistics_business AS a, (SELECT logistics_id AS logisticsId, MAX(statistics_time) AS statisticsTime FROM logistics_business GROUP BY logistics_id) AS b " +
            "WHERE a.logistics_id=b.logisticsId AND a.statistics_time=b.statisticsTime " +
            ") AS lb on l.id=lb.logistics_id where 1=1 " +
            "<if test=\"addressCode!=null and addressCode!='' \">AND l.branch_address_code LIKE CONCAT('%',#{addressCode},'%') </if> " +
            "<if test=\"branchName!=null and branchName!='' \">AND l.branch_name LIKE CONCAT('%',#{branchName},'%') </if> " +
            "<if test=\"statisticsTime!=null and statisticsTime!='' \">" +
            " and lb.statistics_time &gt;= #{statisticsTime}  </if> " +
            "<if test=\"endTime!=null and endTime!='' \">" +
            " and lb.statistics_time &lt; #{endTime}  </if> " +
            "<if test=\"staffsOrder!=null and staffsOrder!='' and (statisticsTimeOrder==null or statisticsTimeOrder=='')\"> ORDER BY l.staffs ${staffsOrder} </if> " +
            "<if test=\"(staffsOrder==null or staffsOrder=='') and statisticsTimeOrder!=null and statisticsTimeOrder!='' \"> ORDER BY lb.statistics_time ${statisticsTimeOrder} </if> " +
            "<if test=\"staffsOrder!=null and staffsOrder!='' and statisticsTimeOrder!=null and statisticsTimeOrder!='' \"> ORDER BY l.staffs ${staffsOrder}, b.statistics_time ${statisticsTimeOrder} </if> " +
            "LIMIT #{offset}, #{size}" +
            "</script>")
    public List<LogisticsBusinessDTO1> getLogisticsLatest(
            @Param(value = "offset") int offset,
            @Param(value = "size") int size,
            @Param(value = "addressCode") String addressCode,
            @Param(value = "branchName") String branchName,
            @Param(value = "statisticsTime") String statisticsTime,
            @Param(value = "endTime") String endTime,
            @Param(value = "staffsOrder") String staffsOrder,
            @Param(value = "statisticsTimeOrder") String statisticsTimeOrder);

    @Select("<script>SELECT " +
            "l.id AS id, l.branch_name  AS branchName, l.branch_address AS branchAddress, " +
            "l.branch_address_code AS branchAddressCode, l.affiliation_id AS affiliationId, l.principal, " +
            "l.contact, l.staffs, l.vehicles, l.delivery_area AS deliveryArea, l.delivery_area_code AS deliveryAreaCode," +
            "lb.id AS logisticsBusinessId, lb.month_out_amount AS monthOutAmount, lb.month_in_amount AS monthInAmount, " +
            "lb.statistics_time AS tradeStatisticsTime " +
            "FROM " +
            "logistics l," +
            "(SELECT a.id,a.`logistics_id`,a.`month_in_amount`,a.`month_out_amount`,a.`statistics_time` " +
            " FROM logistics_business AS a,(SELECT MAX(logistics_id) AS logisticsId,MAX(statistics_time) AS statisticsTime  FROM logistics_business GROUP BY logistics_id) AS b" +
            " WHERE a.logistics_id=b.logisticsId AND a.`statistics_time`=b.statisticsTime " +
            "<if test=\"statisticsTime!=null and statisticsTime!='' \">and b.statistics_time Between #{statisticsTime} and #{endTime} </if> ) AS lb" +

            " WHERE l.id=lb.logistics_id  " +
            "<if test=\"addressCode!=null and addressCode!='' \">AND l.branch_address_code LIKE CONCAT('%',#{addressCode},'%') </if> " +
            "<if test=\"branchName!=null and branchName!='' \">AND l.branch_name LIKE CONCAT('%',#{branchName},'%') </if> " +
            "<if test=\"statisticsTime!=null and statisticsTime!='' \">AND lb.statistics_time &gt;= #{statisticsTime} </if> " +
            "<if test=\"endTime!=null and endTime!='' \">AND lb.statistics_time &lt; #{endTime} </if> " +
            "<if test=\"staffsOrder!=null and staffsOrder!='' and (statisticsTimeOrder==null or statisticsTimeOrder=='')\"> ORDER BY l.staffs ${staffsOrder} </if> " +
            "<if test=\"(staffsOrder==null or staffsOrder=='') and statisticsTimeOrder!=null and statisticsTimeOrder!='' \"> ORDER BY lb.statistics_time ${statisticsTimeOrder} </if> " +
            "<if test=\"staffsOrder!=null and staffsOrder!='' and statisticsTimeOrder!=null and statisticsTimeOrder!='' \"> ORDER BY l.staffs ${staffsOrder}, b.statistics_time ${statisticsTimeOrder} </if> " +
            "</script>")
    public List<Logistics> getLogisticsLatestForExport(
            @Param(value = "addressCode") String addressCode,
            @Param(value = "branchName") String branchName,
            @Param(value = "statisticsTime") String statisticsTime,
            @Param(value = "endTime") String endTime,
            @Param(value = "staffsOrder") String staffsOrder,
            @Param(value = "statisticsTimeOrder") String statisticsTimeOrder);

    /**
     * 根据ID查询物流信息
     *
     * @param id
     * @return 物流信息
     */
    @Select("SELECT id, branch_name AS branchName, branch_address AS branchAddress, branch_address_code AS branchAddressCode, " +
            "affiliation_id AS affiliationId, principal, contact, staffs, vehicles, trade_statistics_time AS tradeStatisticsTime, " +
            "month_out_amount AS monthOutAmount, month_in_amount AS monthInAmount, shipping_order_id AS shippingOrderId, " +
            "shipping_address AS shippingAddress, shipping_address_code AS shippingAddressCode, delivery_area AS deliveryArea, " +
            "delivery_area_code AS deliveryAreaCode, create_time AS createTime, update_time AS updateTime " +
            "FROM logistics WHERE id = #{id}")
    public Logistics getLogisticsById(String id);

    /**
     * 查询最新的物流信息
     *
     * @param logisticsId 物流ID
     * @param count       记录条数
     * @return 物流业务量列表
     */
    @Select("SELECT id, logistics_id AS logisticsId, month_out_amount AS monthOutAmount, month_in_amount AS monthInAmount, statistics_time AS statisticsTime " +
            "FROM logistics_business " +
            "WHERE logistics_id=#{logisticsId} " +
            "ORDER BY statistics_time DESC " +
            "LIMIT #{count}")
    public List<LogisticsBusiness> getLogisticsBusinessLimit(
            @Param(value = "logisticsId") String logisticsId,
            @Param(value = "count") int count);


    /**
     * 物流分析
     *
     * @param addressCode  地址编码
     * @param yearAndMonth 年-月(如：2017-01)
     * @param year         年度（如：2017）
     * @param quarter      季度（如：1）
     * @return
     */
    @Select("<script>" +
            "SELECT l.branch_address_code AS addressCode, g.geo_name AS geoName, DATE_FORMAT(l.create_time,'%Y-%m') AS createTime, " +
            "IFNULL(SUM(lb.monthOutAmount),0) AS monthOutAmount, IFNULL(SUM(lb.monthInAmount),0) AS monthInAmount " +
            "FROM logistics l LEFT JOIN( " +
            "  SELECT a.id, a.logistics_id, b.monthInAmount, b.monthOutAmount, a.statistics_time " +
            "  FROM logistics_business AS a JOIN ( " +
            "    SELECT logistics_id AS logisticsId, SUM(month_in_amount) AS monthInAmount, SUM(month_out_amount) AS monthOutAmount, " +
            "    statistics_time AS statisticsTime " +
            "    FROM logistics_business " +
            "    GROUP BY logistics_id " +
            "  ) AS b ON a.logistics_id=b.logisticsId  AND a.statistics_time=b.statisticsTime " +
            ") AS lb ON l.id=lb.logistics_id " +
            "LEFT JOIN geo_codings g ON l.branch_address_code=g.id " +
            "<where>" +
            "<if test=\"addressCode!=null and addressCode!=''\"> AND l.branch_address_code LIKE #{addressCode} </if>" +
            "<if test=\"yearAndMonth!=null and yearAndMonth!=''\"> AND DATE_FORMAT(l.create_time,'%Y-%m')=#{yearAndMonth} </if>" +
            "<if test=\"year !=null and year!=''\"> AND year(l.create_time) = #{year}</if>" +
            "<if test=\"quarter !=null and quarter!=''\"> AND quarter(l.create_time) = #{quarter}</if>" +
            "</where>" +
            "GROUP BY l.branch_address_code" +
            "</script>")
    public List<HashMap<String, Object>> getLogisticsBusinessAnalysis(
            @Param("addressCode") String addressCode,
            @Param("yearAndMonth") String yearAndMonth,
            @Param("year") String year,
            @Param("quarter") String quarter);
}
